from openpyxl import load_workbook
from openpyxl.styles import Font, Side, Border, PatternFill, GradientFill, Alignment

wb = load_workbook(r'..\Day27-data\student-score1.xlsx')
sheet = wb['成绩表']

# 1. Excel公式写入
# sheet['I2'] = '总分'
# sheet['I3'] = '=sum(D3:H3)'  # Excel公式写法，字符串即可
# for row in range(4, sheet.max_row+1):
#     sheet[f'I{row}'] = f'=sum(D{row}:H{row})'

# 2. 设置单个单元格的字体样式
cell = sheet['C5']
print(cell.font)
font = Font(name='黑体', size=20, bold=True, italic=True, color='66CCFF')
cell.font = font

cells = sheet[2]  # ************
font = Font(name='黑体', size=15, bold=True, color='FF66FF')
for cell in cells:
    cell.font = font

# 3. 设置边框样式
side = Side(border_style='double', color='FFCC00')  # 创建线条对象
border = Border(left=side, right=side, top=side, bottom=side)  # 创建边框对象
for cell in cells:
    cell.border = border

# 4. 设置背景色
pattern_fill = PatternFill(fill_type='solid', fgColor='DDDDDD')
gradient_fill = GradientFill(stop=('FFFFFF', '99CCDD'))  # 渐变
for cell in cells:
    cell.fill = pattern_fill
cell = sheet['A1']
cell.fill = gradient_fill

# 5. 设置文本居中
# horizontal表示水平方向，常用的left center right
# vertical表示垂直方向，常用的top  center  bottom
alignment = Alignment(horizontal='center', vertical='center')
print(sheet.dimensions)
cells = sheet[sheet.dimensions]
# print(cells)
for row in cells:
    for cell in row:
        cell.alignment = alignment

# 6. 设置行高和列宽
print(sheet.row_dimensions)
sheet.row_dimensions[2].height = 40
sheet.column_dimensions['C'].width = 20

wb.save(r'..\Day27-data\student-score1.xlsx')  # 将文件薄对象写入到磁盘
